package com.base.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.List;

import com.base.annotation.DisplayField;
import com.base.annotation.TableName;

public class BeanToSqlutils {

	// sql server
	public static String STARTQUOTE = "[";
	public static String ENDQUOTE = "]";

	// mysql
	// public static String STARTQUOTE="`";
	// public static String ENDQUOTE="`";

	// oracle
	// public static String STARTQUOTE="\"";
	// public static String ENDQUOTE="\"";

	/**
	 * 生成全字段的INSERT语句,不包含ID
	 * 
	 * @param objClass 表类
	 * @param isAutoInc 主键是否自增
	 * @throws SecurityException
	 * @throws ClassNotFoundException
	 */
	public static String getInsertSql(Class<?> objClass, boolean isAutoInc){
		String tableName =getTableName(objClass);
		StringBuilder sb = new StringBuilder();
		StringBuilder sbHead = new StringBuilder();
		StringBuilder sbBody = new StringBuilder();
		Field[] fds = objClass.getDeclaredFields();
		String fieldName;
		for (Field field : fds) {
			if (Modifier.isStatic(field.getModifiers())) {// 去除静态属性
				continue;
			}
			fieldName = field.getName();
			if ("id".equalsIgnoreCase(fieldName) && isAutoInc) {
				continue;
			}
			if (field.isAnnotationPresent(DisplayField.class)) {
				continue;
			}
			sbHead.append(wrapParam(fieldName.toLowerCase()) + ",");
			sbBody.append(":" + fieldName + ",");
		}
		sbHead.deleteCharAt(sbHead.length() - 1);
		sbBody.deleteCharAt(sbBody.length() - 1);
		sb.append("insert into " + tableName + "(").append(sbHead)
				.append(") values(").append(sbBody).append(")");
		return sb.toString();
	}

	/**
	 * 生成全属性字段的更新SQL语句
	 * 
	 * @param otherName表别名
	 * @param objClass 表类
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 */
	public static String getUpdateSql(String otherName, Class<?> objClass){
		String tableName =getTableName(objClass);
		String prefix = "";
		boolean otherNameFlg = false;
		if (otherName != null && !"".equals(otherName.trim())) {
			otherName = otherName.trim();
			prefix = otherName + ".";
			otherNameFlg = true;
		}
		StringBuilder sb = new StringBuilder("update " + tableName).append(
				otherNameFlg ? " " + otherName : "").append(" set ");
		Field[] fds = objClass.getDeclaredFields();
		String fieldName;
		for (Field field : fds) {
			if (Modifier.isStatic(field.getModifiers())) {// 去除静态属性
				continue;
			}
			fieldName = field.getName();
			if ("id".equalsIgnoreCase(fieldName)) {
				continue;
			}
			if (field.isAnnotationPresent(DisplayField.class)) {
				continue;
			}
			sb.append(prefix + wrapParam(fieldName.toLowerCase())
					+ "=:" + fieldName + ",");
		}
		sb.deleteCharAt(sb.length() - 1);
		return sb.toString();
	}

	/**
	 * 生成去掉部分属性字段的更新SQL语句前半部字段
	 * @param otherName    表别名
	 * @param objClass   表类
	 * @param exceptFields不包含的字段
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 */
	public static String getUpdateSqlStr(String otherName, Class<?> objClass,
			List<String> exceptFields){
		String tableName =getTableName(objClass);
		String prefix = "";
		boolean otherNameFlg = false;
		if (otherName != null && !"".equals(otherName.trim())) {
			otherName = otherName.trim();
			prefix = otherName + ".";
			otherNameFlg = true;
		}
		StringBuilder sb = new StringBuilder("update " + tableName).append(
				otherNameFlg ? " " + otherName : "").append(" set ");
		Field[] fds = objClass.getDeclaredFields();
		String fieldName;
		for (Field field : fds) {
			if (Modifier.isStatic(field.getModifiers())) {// 去除静态属性
				continue;
			}
			fieldName = field.getName();
			if ("id".equalsIgnoreCase(fieldName)) {
				continue;
			}
			if (field.isAnnotationPresent(DisplayField.class)) {
				continue;
			}
			if (exceptFields != null && exceptFields.contains(fieldName)) {
				continue;
			}
			sb.append(prefix + wrapParam(fieldName.toLowerCase())
					+ "=:" + fieldName + ",");
		}
		sb.deleteCharAt(sb.length() - 1);
		return sb.toString();
	}

	/**
	 * 生成全属性字段的查询SQL语句
	 * @param otherName     表别名
	 * @param objClass   表类
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 */
	public static String getSelectSql(String otherName, Class<?> objClass){
		String tableName =getTableName(objClass);
		String prefix = "";
		boolean otherNameFlg = false;
		if (otherName != null && !"".equals(otherName.trim())) {
			otherName = otherName.trim();
			prefix = otherName + ".";
			otherNameFlg = true;
		}
		StringBuilder sb = new StringBuilder("select ");
		Field[] fds = objClass.getDeclaredFields();
		String fieldName;
		for (Field field : fds) {
			if (Modifier.isStatic(field.getModifiers())) {// 去除静态属性
				continue;
			}
			if (field.isAnnotationPresent(DisplayField.class)) {
				continue;
			}
			fieldName = field.getName();
			sb.append(prefix + wrapParam(fieldName.toLowerCase())
					+ ",");
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(" from " + tableName).append(
				otherNameFlg ? " " + otherName : "");
		return sb.toString();
	}

	/**
	 * 生成去掉一些属性字段的select语句中的栏位罗列字段
	 * @param otherName   表别名
	 * @param objClass 表类
	 * @param exceptFields 不包含的字段
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 */
	public static String getSelectSqlStr(String otherName, Class<?> objClass,
			List<String> exceptFields) {
		String prefix = "";
		if (otherName != null && !"".equals(otherName.trim())) {
			otherName = otherName.trim();
			prefix = otherName + ".";
		}
		StringBuilder sb = new StringBuilder("");
		Field[] fds = objClass.getDeclaredFields();
		String fieldName;
		for (Field field : fds) {
			if (Modifier.isStatic(field.getModifiers())) {// 去除静态属性
				continue;
			}
			if (field.isAnnotationPresent(DisplayField.class)) {
				continue;
			}
			fieldName = field.getName();
			if (exceptFields != null && exceptFields.contains(fieldName)) {
				continue;
			}

			sb.append(prefix + wrapParam(fieldName.toLowerCase()) + ",");
		}
		sb.deleteCharAt(sb.length() - 1);
		return sb.toString();
	}
	
	/**
	 * 获得entity对应的数据库名称
	 * @param objClass
	 * @return
	 */
	public static String getTableName(Class<?> objClass){
		TableName tableName=objClass.getAnnotation(TableName.class);
		if(tableName !=null){
			return wrapParam(tableName.value());
		}else{
			return wrapParam(ReflectUtils.getClassName(objClass).toLowerCase());
		}
	}
	
	/**
	 * 相等条件拼装
	 * @param fieldName 栏位
	 * @param alias 表别名
	 * @return
	 */
	public static String equalCondition(String fieldName,String alias){
		return assembleCondition(fieldName,fieldName, alias, "=");
	}
	
	/**
	 * like条件拼装
	 * @param fieldName 栏位
	 * @param alias 表别名
	 * @return
	 */
	public static String likeCondition(String fieldName,String alias){
		return assembleCondition(fieldName,fieldName, alias, "like");
	}
	
	/**
	 * 提供各种条件拼装
	 * @param fieldName 栏位
	 * @param fieldAlias 命名参数
	 * @param alias 表别名
	 * @param type 条件类型
	 * @return
	 */
	public static String assembleCondition(String fieldName,String fieldAlias,String alias,String type){
		if(alias==null || "".equals(alias)){
			return wrapParam(fieldName.toLowerCase())+" "+type+" :"+fieldAlias;
		}else{
			return alias+"."+wrapParam(fieldName.toLowerCase())+" "+type+" :"+fieldAlias;
		}
	}
	
	public static String wrapParam(String param){
		return STARTQUOTE+param+ENDQUOTE;
	}
}
